﻿Imports System.Data.SqlClient
Imports System.IO
Public Class XL_DULIEU
    Public Shared strKetNoi As String = "Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=Northwind;server=(local)"

    Public Shared Sub GanCauLenhKetNoi(ByVal s As String)
        strKetNoi = s
    End Sub
    Public Shared Sub GanCauLenhKetNoi(ByVal sUserName As String, ByVal sPassword As String)
        ThayTheGiaTri("User ID", sUserName)
        ThayTheGiaTri("Password", sPassword)
    End Sub
    'ham dung de thay gia tri cua mot thanh phan trong chuoi
    Public Shared Sub ThayTheGiaTri(ByVal sThanhPhan As String, ByVal sGiaTri As String)
        Dim sThayThe As String = sThanhPhan & "=" & sGiaTri
        Dim viTriDau As Integer = strKetNoi.IndexOf(sThanhPhan)
        'neu tim thay
        If (viTriDau <> -1) Then
            'delete het cho den khi gap ";" hoac cuoi chuoi
            'tim vi tri cuoi cua chuoi nay

            Dim i As Integer = 0
            While (viTriDau + i < strKetNoi.Length)
                If (strKetNoi(viTriDau + i) <> ";") Then
                    i += 1
                Else
                    Exit While
                End If
            End While
            'delete
            strKetNoi = strKetNoi.Remove(viTriDau, i)
            'thay the bang chuoi moi
            strKetNoi = strKetNoi.Insert(viTriDau, sThayThe)
        Else
            sThayThe = ";" & sThayThe
            strKetNoi = String.Concat(strKetNoi, sThayThe)
        End If
    End Sub
    'ham dung de doc du lieu va do vao mot bang
    Public Shared Function LayDuLieuRaBang(ByVal strCauLenh As String, ByRef bang As DataTable) As Boolean
        Dim kq As Boolean = True
        Try
            Dim adapt As New SqlDataAdapter(strCauLenh, strKetNoi)
            'do du lieu ra bang
            adapt.FillSchema(bang, SchemaType.Source)
            adapt.Fill(bang)
        Catch ex As Exception
            'ghi khong thanh cong
            kq = False
        End Try
        Return kq
    End Function

    'ham dung de ghi du lieu cua bang vao co so du lieu chinh
    Public Shared Function GhiBang(ByRef bang As DataTable, ByVal tenBang As String) As Boolean
        Dim kq As Boolean = True
        Dim strCauLenh As String = "select * from " + tenBang
        Try
            Dim adapt As New SqlDataAdapter(strCauLenh, strKetNoi)
            Dim boDocGhi As New SqlCommandBuilder(adapt)
            'ghi bang vao co so du lieu
            adapt.Update(bang)
        Catch ex As Exception
            kq = False
        End Try
        Return kq
    End Function

    'ham thuc hien mot lenh nao do cua SQL
    Public Shared Function ThucHienLenh(ByVal strCauLenh As String) As Boolean
        Dim kq As Boolean = True

        'tao ra cau lenh de thuc hien
        Dim cmd As New SqlCommand()
        Dim cnKetNoi As SqlConnection = New SqlConnection(strKetNoi)
        cmd.Connection = cnKetNoi
        cmd.CommandType = CommandType.Text
        cmd.CommandText = strCauLenh
        Try
            'thuc hien lenh
            cnKetNoi.Open()
            cmd.ExecuteNonQuery()
            cnKetNoi.Close()
        Catch ex As Exception
            kq = False
        End Try
        Return kq
    End Function

    'lay gia tri cua viec thuc thi mot Store procedure
    Public Shared Function LayGiaTri(ByVal strCauLenh As String) As Object
        Dim kq As Object

        'tao ra cau lenh de thuc hien
        Dim cmd As New SqlCommand()
        Dim cnKetNoi As SqlConnection = New SqlConnection(strKetNoi)
        cmd.Connection = cnKetNoi
        cmd.CommandType = CommandType.Text
        cmd.CommandText = strCauLenh

        Try
            'thuc hien lenh
            cnKetNoi.Open()
            kq = cmd.ExecuteScalar()
            cnKetNoi.Close()
        Catch ex As Exception
            kq = Nothing
        End Try
        Return kq
    End Function
    'lay Connection String tu file UDL
    Public Shared Function LayConnectionString(ByVal fileName As String) As String
        Dim mCnnStr As String = ""
        Try
            Dim sr As StreamReader = New StreamReader(fileName)
            Dim line As String
            Do
                line = sr.ReadLine()
                If Not IsNothing(line) Then
                    Dim str As String = "Provider=SQLOLEDB.1;"
                    Dim index = line.IndexOf(str) + str.Length()
                    If index = str.Length() Then
                        mCnnStr = line.Substring(index)
                        Exit Do
                    End If
                End If
            Loop Until line Is Nothing
            sr.Close()
        Catch ex As Exception
            MessageBox.Show("Không đọc được dữ liệu! Lỗi", "Thông báo", MessageBoxButtons.OK)
        End Try
        Return mCnnStr
    End Function

    Public Shared Function GetConnectStrFromFile(ByVal pFileSpec As String) As String
        Dim mPassFromFile As String = ""
        Dim mCnnStr As String = ""
        Try
            Dim sr As StreamReader = New StreamReader(pFileSpec)
            Dim line As String
            Dim l As Integer = 0
            Do
                line = sr.ReadLine()
                l += 1
                If (l = 3) Then
                    mCnnStr = line
                    Exit Do
                End If
            Loop Until line Is Nothing
            sr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return mCnnStr
    End Function

    Public Shared Function LayGiaTriThanhPhan(ByVal sTenThanhPhan As String) As String
        'tuy theo gia tri ma phai chuyen ve dung dang cua no
        Dim kq As String = ""
        Dim vt1 As Integer = 0
        Dim vt2 As Integer = 0
        Dim temp = sTenThanhPhan & "="
        'vi tri 1 se la vi tri cua Cum tu Intital Catalog=
        vt1 = strKetNoi.IndexOf(temp)
        'vi tri 2 se la vi tri cua dau; ket thuc cho cum "Initial Catalog=<ten DB>;"
        vt2 = strKetNoi.IndexOf(";", vt1)
        'Neu khong tim thay tuc la cuoi chuoi tim kiem
        If vt2 = -1 Then
            vt2 = strKetNoi.Length
        End If
        kq = strKetNoi.Substring(vt1 + temp.Length, vt2 - vt1 - temp.Length)
        kq = kq.Trim()
        Return kq
    End Function
End Class
